package com.mybatis.student;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ScoreManage {
    public static void insertStudents(SqlSession session, List<Score> scores) {
        for (Score score : scores) {
            // 查询是否存在相同学号的记录
            Score existingScore = session.selectOne("queryStudentByNumber", score);
            if (existingScore == null) {
                session.insert("addStudent1", score); // 插入单个记录
            } else {
                System.out.println("学号 " + score.getNumber() + " 已存在，跳过插入");
            }
        }
    }

    // 其他方法

    public static void updateStudent(SqlSession session, Score score) {
        session.update("updateStudentByNumber", score);
    }

    public static List<Score> redscore() throws IOException {
        List<Score> scores = new ArrayList<>();
        // 使用相对路径或资源文件
        String resourcePath = "students.xlsx"; // 假设文件在resources目录下
        InputStream inputStream = Resources.getResourceAsStream(resourcePath);
        if (inputStream == null) {
            throw new FileNotFoundException("文件未找到: " + resourcePath);
        }
        // 检查文件是否为有效的 Excel 文件
        if (!resourcePath.endsWith(".xlsx")) {
            throw new IllegalArgumentException("文件不是有效的 Excel 文件: " + resourcePath);
        }
        // 验证文件是否为有效的 OOXML 文件
        try (OPCPackage pkg = OPCPackage.open(inputStream)) {
            try (XSSFWorkbook workbook = new XSSFWorkbook(pkg)) {
                XSSFSheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
                for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 从第二行开始读取数据
                    org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
                    if (row != null) {
                        Score score = new Score();
                        // 处理学号
                        org.apache.poi.ss.usermodel.Cell numberCell = row.getCell(0);
                        if (numberCell != null) {
                            score.setNumber(getCellValueAsString(numberCell));
                        }
                        // 处理姓名
                        org.apache.poi.ss.usermodel.Cell nameCell = row.getCell(1);
                        if (nameCell != null) {
                            score.setStudentName(getCellValueAsString(nameCell)); // 修改为 setStudentName
                        }
                        // 处理性别
                        org.apache.poi.ss.usermodel.Cell genderCell = row.getCell(2);
                        if (genderCell != null) {
                            score.setGender(getCellValueAsString(genderCell));
                        }
                        // 处理年龄
                        org.apache.poi.ss.usermodel.Cell ageCell = row.getCell(3);
                        if (ageCell != null) {
                            score.setAge((int) getCellValueAsNumeric(ageCell));
                        }
                        // 处理班级
                        org.apache.poi.ss.usermodel.Cell classNameCell = row.getCell(4);
                        if (classNameCell != null) {
                            score.setClassName(getCellValueAsString(classNameCell));
                        }
                        // 处理语文成绩
                        org.apache.poi.ss.usermodel.Cell chineseCell = row.getCell(5);
                        if (chineseCell != null) {
                            score.setChinese(getCellValueAsString(chineseCell));
                        }
                        // 处理数学成绩
                        org.apache.poi.ss.usermodel.Cell mathCell = row.getCell(6);
                        if (mathCell != null) {
                            score.setMath(getCellValueAsString(mathCell));
                        }
                        // 处理英语成绩
                        org.apache.poi.ss.usermodel.Cell englishCell = row.getCell(7);
                        if (englishCell != null) {
                            score.setEnglish(getCellValueAsString(englishCell));
                        }
                        scores.add(score);
                    }
                }
            }
        } catch (Exception e) {
            throw new IOException("文件不是有效的 Excel 文件: " + resourcePath + ". 请确保文件未损坏且格式正确。", e);
        }
        return scores;
    }

    private static String getCellValueAsString(org.apache.poi.ss.usermodel.Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static double getCellValueAsNumeric(org.apache.poi.ss.usermodel.Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                try {
                    return Double.parseDouble(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return 0;
                }
            case NUMERIC:
                return cell.getNumericCellValue();
            default:
                return 0;
        }
    }

    public static void main(String[] args) throws IOException {
        InputStream inputStream;
        inputStream = Resources.getResourceAsStream("config/student-config.xml");
        SqlSessionFactory ssf;
        ssf = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = ssf.openSession(true);

        List<Score> scores = redscore();
        if (!scores.isEmpty()) {
            insertStudents(session, scores);
            System.out.println("成功插入 " + scores.size() + " 条记录到 student1 表中");
        } else {
            System.out.println("没有读取到任何学生记录，请检查 Excel 文件内容！");
        }

        // 查询所有的学生信息记录
        List<Score> allScores = session.selectList("queryStudent1");
        System.out.println("查询所有的学生信息记录:");
        for (Score score : allScores) {
            System.out.println(score);
        }

        // 根据学号查询学生信息记录
        Score score1 = new Score();
        score1.setNumber("20220102001");
        Score result_score = session.selectOne("queryStudentByNumber", score1); // 使用 queryStudentByNumber
        System.out.println("查询学号为20220102001的学生信息记录:");
        System.out.println(result_score);

        // 根据班级查询学生信息记录
        Score score2 = new Score();
        score2.setClassName("班级1");
        List<Score> classScores = session.selectList("queryStudent1", score2);
        System.out.println("查询班级为班级1的学生信息记录:");
        for (Score score : classScores) {
            System.out.println(score);
        }

        // 根据性别查询学生信息记录
        Score score3 = new Score();
        score3.setGender("男");
        List<Score> genderScores = session.selectList("queryStudent1", score3);
        System.out.println("查询性别为男的学生信息记录:");
        for (Score score : genderScores) {
            System.out.println(score);
        }

        // 更新学生信息记录
        Score updateScore = new Score();
        updateScore.setNumber("20220102001");
        updateScore.setStudentName("张三"); // 使用 setStudentName
        updateScore.setGender("男");
        updateScore.setAge(20); // 如果 age 列确实存在，可以保留；否则需要移除
        updateStudent(session, updateScore);
        System.out.println("更新学号为20220102001的学生信息记录");

        session.close();
    }
}
